import java.util.*;
import java.sql.*;

public class ByCategory {

	public static void main(String[] args) throws ClassNotFoundException,SQLException, Exception {
		byCat();
	}

	public static void byCat() throws ClassNotFoundException,SQLException {
		Scanner input = new Scanner(System.in);	
		ResultSet r1 = null;	
		ResultSet catTable = null;
		ResultSet itemTable = null;
		String category = null;

		//Connect to Database
		Connection c = mysqlConnection.connect();

		// Outputs table of categories; Assuming that we will add some information to the database so that the table will never be empty.
		PreparedStatement ps1 = c.prepareStatement("SELECT Name FROM Categories");
		catTable = ps1.executeQuery();
		System.out.println();
		while(catTable.next()){
			System.out.println(catTable.getString(1));
		} 		
		
		//Select Category
		while (r1 == null || !(r1.next())) {
			System.out.print("\nPlease enter the name for the category you'd like to search for: ");
			category = input.nextLine();
			PreparedStatement findCat = c.prepareStatement("SELECT CategoryID FROM Categories WHERE Name = ?");
			findCat.setString(1, category);
			r1 = findCat.executeQuery();
			
			if(!r1.next())
				System.out.println("\nCategory was not entered correctly. Please try again.");
			else
				break;
		}

		// Outputs table of items by category selected earlier
		PreparedStatement ps2 = c.prepareStatement("SELECT ItemID FROM TradeItem WHERE CategoryID = ?");
		r1.first();
		int cat = r1.getInt(1);
		ps2.setInt(1, cat);
		itemTable = ps2.executeQuery();
		if(itemTable == null)
			System.out.println("\nNo items found in this category.");
		System.out.println("Item IDs: ");

		while(itemTable.next()){
			System.out.println(itemTable.getString(1));
		}
		
	}
}
